/*================================================
	Creator: Tony & Janet Rose
	Date: 2012.03.12
	Use: Setup JaxDug Database and Tables
================================================*/

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'jaxdug-test')
CREATE DATABASE [jaxdug-test];
GO

USE [jaxdug-test]
GO

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'VoteObject')
CREATE TABLE [dbo].[VoteObject](
Id INT NOT NULL IDENTITY(1,1),
VoteObjectCreated date,
PRIMARY KEY(Id)
);

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'User')
CREATE TABLE [dbo].[User](
UserId int NOT NULL IDENTITY(1,1),
UserName varchar(100) NOT NULL,
FirstName varchar(100) NOT NULL,
LastName varchar(100) NOT NULL,
Email varchar(100) NOT NULL,
IsAdmin bit NOT NULL,
UserCreated date,
PRIMARY KEY(UserId)
);

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Presentation')
CREATE TABLE [dbo].[Presentation](
PresentationId int FOREIGN KEY REFERENCES VoteObject(Id),
SpeakerId int FOREIGN KEY REFERENCES [User](UserId),
Category varchar(50),
Topic varchar(255),
Content varchar(max),
ScheduledDate date,
UserId int NOT NULL FOREIGN KEY REFERENCES [User](UserId),
PresentationCreated date
);

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Book')
CREATE TABLE [dbo].[Book](
BookId int NOT NULL FOREIGN KEY REFERENCES VoteObject(Id),
ISBN varchar(14), 
Title varchar(255),
Author varchar(255),
[Description] varchar(max),
AmazonUrl varchar(255),
CoverImageUrl varchar(255),
LastAmazonUpdate date,
--UserId int NOT NULL FOREIGN KEY REFERENCES [User](UserId),
BookCreated date
);

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Vote')
CREATE TABLE [dbo].[Vote](
VoteId int NOT NULL IDENTITY(1,1),
VoteObjectId int NOT NULL FOREIGN KEY REFERENCES VoteObject(Id),
Choice int,
UserId int FOREIGN KEY REFERENCES [User](UserId),
VoteCreated date
PRIMARY KEY(VoteId)
);

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Comment')
CREATE TABLE [dbo].[Comment](
CommentId int NOT NULL IDENTITY(1,1),
VoteObjectId int not null FOREIGN KEY REFERENCES VoteObject(Id),
Content varchar(max),
UserId int NOT NULL FOREIGN KEY REFERENCES [User](UserId),
CommentCreated date
PRIMARY KEY(CommentId)
);


-- ==================================================================
-- Author:		RbH
-- Create date: 2012.03.30
-- Description:	Creates the database
-- Notes:		These two sets of scripts need to be manually merged.
--				Until that time, leave this commented out. After the
--				merge we can script out the new database at once.
-- ==================================================================

/*
USE [master]
GO

/****** Object:  Database [JaxDug]    Script Date: 03/30/2012 14:51:58 ******/
CREATE DATABASE [JaxDug] ON  PRIMARY 
( NAME = N'JaxDug', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JaxDug.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'JaxDug_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JaxDug_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [JaxDug] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [JaxDug].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [JaxDug] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [JaxDug] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [JaxDug] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [JaxDug] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [JaxDug] SET ARITHABORT OFF 
GO

ALTER DATABASE [JaxDug] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [JaxDug] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [JaxDug] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [JaxDug] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [JaxDug] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [JaxDug] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [JaxDug] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [JaxDug] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [JaxDug] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [JaxDug] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [JaxDug] SET  DISABLE_BROKER 
GO

ALTER DATABASE [JaxDug] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [JaxDug] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [JaxDug] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [JaxDug] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [JaxDug] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [JaxDug] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [JaxDug] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [JaxDug] SET  READ_WRITE 
GO

ALTER DATABASE [JaxDug] SET RECOVERY FULL 
GO

ALTER DATABASE [JaxDug] SET  MULTI_USER 
GO

ALTER DATABASE [JaxDug] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [JaxDug] SET DB_CHAINING OFF 
GO

USE [JaxDug]
GO
/****** Object:  ForeignKey [FK_Presentations_Locations]    Script Date: 03/30/2012 14:52:30 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Presentations_Locations]') AND parent_object_id = OBJECT_ID(N'[dbo].[Presentations]'))
ALTER TABLE [dbo].[Presentations] DROP CONSTRAINT [FK_Presentations_Locations]
GO
/****** Object:  Table [dbo].[Presentations]    Script Date: 03/30/2012 14:52:30 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Presentations_Locations]') AND parent_object_id = OBJECT_ID(N'[dbo].[Presentations]'))
ALTER TABLE [dbo].[Presentations] DROP CONSTRAINT [FK_Presentations_Locations]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Presentations]') AND type in (N'U'))
DROP TABLE [dbo].[Presentations]
GO
/****** Object:  Table [dbo].[PresentationTags]    Script Date: 03/30/2012 14:52:30 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PresentationTags]') AND type in (N'U'))
DROP TABLE [dbo].[PresentationTags]
GO
/****** Object:  Table [dbo].[Tags]    Script Date: 03/30/2012 14:52:30 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tags]') AND type in (N'U'))
DROP TABLE [dbo].[Tags]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 03/30/2012 14:52:30 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
DROP TABLE [dbo].[Users]
GO
/****** Object:  Table [dbo].[Locations]    Script Date: 03/30/2012 14:52:30 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Locations]') AND type in (N'U'))
DROP TABLE [dbo].[Locations]
GO
/****** Object:  Table [dbo].[Locations]    Script Date: 03/30/2012 14:52:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Locations]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Locations](
	[LocationID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](50) NOT NULL,
	[StreetAddress1] [nvarchar](50) NOT NULL,
	[StreetAddress2] [nvarchar](50) NULL,
	[City] [nvarchar](50) NOT NULL,
	[State] [nchar](2) NOT NULL,
	[Zip] [nchar](5) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[LocationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[Users]    Script Date: 03/30/2012 14:52:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Users](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[Salutation] [nchar](4) NULL,
	[FirstName] [nchar](50) NOT NULL,
	[LastName] [nchar](50) NOT NULL,
	[Bio] [nvarchar](max) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[Tags]    Script Date: 03/30/2012 14:52:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tags]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Tags](
	[TagID] [int] IDENTITY(1,1) NOT NULL,
	[Tag] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Tags] PRIMARY KEY CLUSTERED 
(
	[TagID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[PresentationTags]    Script Date: 03/30/2012 14:52:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PresentationTags]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PresentationTags](
	[PresentationID] [int] NOT NULL,
	[TagID] [int] NOT NULL
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[Presentations]    Script Date: 03/30/2012 14:52:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Presentations]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Presentations](
	[PresentationID] [int] IDENTITY(1,1) NOT NULL,
	[EnteredDate] [date] NOT NULL,
	[Title] [nvarchar](250) NOT NULL,
	[Synopsis] [nvarchar](max) NOT NULL,
	[SpeakerID] [int] NULL,
	[StartDateTime] [datetime2](0) NULL,
	[EndDateTime] [datetime2](0) NULL,
	[LocationID] [int] NULL,
 CONSTRAINT [PK_Presentations] PRIMARY KEY CLUSTERED 
(
	[PresentationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  ForeignKey [FK_Presentations_Locations]    Script Date: 03/30/2012 14:52:30 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Presentations_Locations]') AND parent_object_id = OBJECT_ID(N'[dbo].[Presentations]'))
ALTER TABLE [dbo].[Presentations]  WITH CHECK ADD  CONSTRAINT [FK_Presentations_Locations] FOREIGN KEY([LocationID])
REFERENCES [dbo].[Locations] ([LocationID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Presentations_Locations]') AND parent_object_id = OBJECT_ID(N'[dbo].[Presentations]'))
ALTER TABLE [dbo].[Presentations] CHECK CONSTRAINT [FK_Presentations_Locations]
GO
*/